IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[cms_sp_PageSelectOne]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[cms_sp_PageSelectOne]
GO
create procedure [dbo].[cms_sp_PageSelectOne]
(
@PageId int, 
@PublicPageId int = null,
@SiteGroupId int,
@SiteId int,
@SystemLanguageId int = null,
@PageTypeParameter nvarchar(400),
@PageControlTypeParameter nvarchar(150),
@IsInAdministration bit = null
)
as
declare @TemplateId int
declare @IsPublished bit
declare @IsLatestRevision bit
declare @IsLatestCompletedRevision bit
declare @WorkflowId uniqueidentifier
declare @PublishStartDate datetime
declare @PublishEndDate datetime
if @IsInAdministration is null
begin
	set @IsInAdministration = 0
end
if @PublicPageId is not null
begin
	select 
		@PageId = Pages.PageId
	from [dbo].[Pages]
	inner join [dbo].[EntityRevisions]
		on EntityRevisions.EntityId = Pages.PageId and EntityRevisions.EntityTypeParameter = 'Page'
	where (([SiteGroupId] is null and [SiteId] is null) or 
		([SiteGroupId] = @SiteGroupId and [SiteId] is null) or
		([SiteGroupId] = @SiteGroupId and [SiteId] = @SiteId)) and 
		((@IsInAdministration = 1 and EntityRevisions.IsLatestRevision = 1) or
			(@IsInAdministration = 0 and EntityRevisions.IsLatestCompletedRevision = 1)) and
		((@IsInAdministration = 1) or
		(@IsInAdministration = 0 and EntityRevisions.IsPublished = 1)) and
		EntityRevisions.IsDeleted = 0 and
		Pages.PublicPageId = @PublicPageId
end
select 
	@TemplateId = Pages.TemplateId,
	@PublishStartDate = EntityRevisions.PublishStartDate,
	@PublishEndDate = EntityRevisions.PublishEndDate,
	@IsLatestRevision = EntityRevisions.IsLatestRevision,
	@IsLatestCompletedRevision = EntityRevisions.IsLatestCompletedRevision,
	@WorkflowId = EntityRevisions.WorkflowId,
	@IsPublished = EntityRevisions.IsPublished
from [dbo].[Pages]
inner join [dbo].[EntityRevisions]
	on EntityRevisions.EntityId = Pages.PageId and EntityRevisions.EntityTypeParameter = @PageTypeParameter
where (([SiteGroupId] is null and [SiteId] is null) or 
	([SiteGroupId] = @SiteGroupId and [SiteId] is null) or
	([SiteGroupId] = @SiteGroupId and [SiteId] = @SiteId)) and 	
	EntityRevisions.IsDeleted = 0 and
	Pages.PageId = @PageId
select 
	Pages.PageId,
	Pages.PublicPageId,
	(select 
		ChildPages.PageId
	from dbo.Pages as ChildPages
	inner join dbo.EntityRevisions
	on EntityRevisions.EntityId = ChildPages.PageId and EntityRevisions.EntityTypeParameter = @PageTypeParameter and
		((@IsInAdministration = 1 and EntityRevisions.IsLatestRevision = 1) or
		(@IsInAdministration = 0 and EntityRevisions.IsLatestCompletedRevision = 1))
	where ChildPages.PageNodeId = Pages.PageNodeId.GetAncestor(1) and
		((ChildPages.[SiteGroupId] is null and ChildPages.[SiteId] is null) or 
		(ChildPages.[SiteGroupId] = @SiteGroupId and ChildPages.[SiteId] is null) or
		(ChildPages.[SiteGroupId] = @SiteGroupId and ChildPages.[SiteId] = @SiteId))) as ParentPageId,
	(select 
		ChildPages.PageName
	from dbo.Pages as ChildPages
	inner join dbo.EntityRevisions
	on EntityRevisions.EntityId = ChildPages.PageId and EntityRevisions.EntityTypeParameter = @PageTypeParameter and
		((@IsInAdministration = 1 and EntityRevisions.IsLatestRevision = 1) or
		(@IsInAdministration = 0 and EntityRevisions.IsLatestCompletedRevision = 1))
	where ChildPages.PageNodeId = Pages.PageNodeId.GetAncestor(1) and
		((ChildPages.[SiteGroupId] is null and ChildPages.[SiteId] is null) or 
		(ChildPages.[SiteGroupId] = @SiteGroupId and ChildPages.[SiteId] is null) or
		(ChildPages.[SiteGroupId] = @SiteGroupId and ChildPages.[SiteId] = @SiteId))) as ParentPageName,
	Pages.SiteGroupId,
	Pages.SiteId,
	Pages.SystemLanguageId,	
	Pages.PageTypeKey,
	Pages.PageBehaviourTypeKey,
	Pages.PageNodeId.GetLevel() as PageLevel,
	Pages.PageName,
	Pages.PageUrl,
	Pages.IsVisible,
	Pages.UseFileCache,
	Pages.PageTitle,
	Pages.PageTitleOverride,
	Pages.PageDescription,
	Pages.PageKeywords,
	Pages.CreatedBy,
	Pages.DateCreated,
	Pages.LastUpdatedBy,
	Pages.DateLastUpdated,
	@IsLatestRevision as IsLatestRevision,
	@IsLatestCompletedRevision as IsLatestCompletedRevision,
	@WorkflowId as WorkflowId,
	@IsPublished as IsPublished,
	@PublishStartDate as PublishStartDate,
	@PublishEndDate as PublishEndDate,
	Templates.TemplateId,
	Templates.TemplateName,
	Templates.TemplateTypeKey,
	Templates.TemplateUrl
from dbo.Pages
inner join dbo.Templates
	on Templates.TemplateId = Pages.TemplateId
where Pages.PageId = @PageId and
	((Pages.SiteGroupId is null and Pages.SiteId is null) or 
	(Pages.SiteGroupId = @SiteGroupId and Pages.SiteId is null) or
	(Pages.SiteGroupId = @SiteGroupId and Pages.SiteId = @SiteId))
select
	@PageId as PageId,
	TemplateRegions.TemplateRegionId,
	TemplateRegions.TemplateRegionName,
	@TemplateId as TemplateId,
	InnerPageControls.PageControlId,
	InnerPageControls.ModuleId,
	InnerPageControls.PageControlTypeKey,
	InnerPageControls.PageControlViewValue,
	InnerPageControls.PageControlModelValue,
	InnerPageControls.PageControlContentId,
	InnerPageControls.PageControlOrder,
	InnerPageControls.PageControlContent,
	InnerPageControls.AdminCaption
from dbo.TemplateRegions
left join 
	(select		
		PageControls.PageControlId,
		PageControls.PageId,
		PageControls.TemplateRegionId,
		PageControls.ModuleId,
		PageControls.PageControlTypeKey,
		PageControls.PageControlViewValue,
		PageControls.PageControlModelValue,
		PageControls.PageControlContentId,
		PageControls.PageControlOrder,
		PageControls.AdminCaption,
		(select			
			Contents.ContentBody 
		from dbo.Contents
		where Contents.ContentId = PageControls.PageControlContentId
		) as PageControlContent
	from dbo.PageControls
	inner join dbo.EntityRevisions
		on EntityRevisions.EntityId = PageControls.PageControlId and 
		EntityRevisions.EntityTypeParameter = @PageControlTypeParameter
	where ((@IsInAdministration = 1 and EntityRevisions.IsLatestRevision = 1) or
		(@IsInAdministration = 0 and EntityRevisions.IsLatestCompletedRevision = 1)) and
		((@IsInAdministration = 1) or
		(@IsInAdministration = 0 and EntityRevisions.IsPublished = 1)) and
		EntityRevisions.IsDeleted = 0) as InnerPageControls
	on InnerPageControls.PageId = @PageId and InnerPageControls.TemplateRegionId = TemplateRegions.TemplateRegionId
where TemplateRegions.TemplateId = @TemplateId 
order by TemplateRegions.TemplateRegionId, InnerPageControls.PageControlOrder


GO

